# this script creates all the tables needed by the wikifolio project
# ------
# to re-create the database from scratch, delete databases first,
# then run this script, then run write_csv_of_stocks_to_vadb.R,
# then run write_stockprices_to_vadb.R

# stockname
sql1 <- "CREATE TABLE IF NOT EXISTS `stockname` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `symbol` varchar(10) CHARACTER SET ascii NOT NULL,
  `name` varchar(200) CHARACTER SET utf8 NOT NULL,
  `WKN` varchar(6) DEFAULT NULL,
  `ISIN` varchar(12) DEFAULT NULL,
  `trickySplit` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `symbol` (`symbol`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci"

# stockprice
sql2 <- "CREATE TABLE IF NOT EXISTS `stockprice` (
  `stock_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `open` float(12,4) DEFAULT NULL,
  `high` float(12,4) DEFAULT NULL,
  `low` float(12,4) DEFAULT NULL,
  `close` float(12,4) DEFAULT NULL,
  `volume` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`stock_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci"

# dividends
sql3 <- "CREATE TABLE IF NOT EXISTS `dividends` (
  `stock_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `dividend` float(12,4) DEFAULT NULL,
  PRIMARY KEY (`stock_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci"

# splits
sql4 <- "CREATE TABLE IF NOT EXISTS `splits` (
  `stock_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `ratio` float(12,4) DEFAULT NULL,
  PRIMARY KEY (`stock_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci"

con <- connectToVadb(dbname="va_wikifolio")

dbSendQuery(con, sql1)
dbSendQuery(con, sql2)
dbSendQuery(con, sql3)
dbSendQuery(con, sql4)

disconnectFromVadb(con)